<?php
class MITStock extends CI_Model{

	public function __construct()
    {
        parent::__construct();
    }
    
    function getItemByStockIdItemId($id, $itemId){
	    $data = array();
	    $this->db->where('stock_id',id_clean($id));
        $this->db->where('itused_id',id_clean($itemId));
	    $this->db->limit(1);
	    $Q = $this->db->get('itused');
	    if ($Q->num_rows() > 0){
	      $data = $Q->row_array();
	    }
	    $Q->free_result();    
	    return $data;    
	 }
	 
     function getStockInfo()
     {
        //$sql = "SELECT count(status_id) as cnt, status_id FROM `stock` WHERE createdby='".$_SESSION['username']."' AND branch_id='".$_SESSION['branch']."' GROUP BY status_id";
        $sql = "SELECT count(status_id) as cnt, status_id FROM `stock` GROUP BY status_id";
        $query = $this->db->query($sql);
        return $query->result();
     }
     
     function getReportIT($cat, $start = '', $end = '', $branch = '')
     {
         $sql = "SELECT `itstock`.`stock_id` , `itstock`.`item` , `itstock`.`unit`, `itstock`.`total`,\n";
         $sql .= "sum((SELECT SUM(COALESCE(unit,0)) FROM itused where stock_id=`itstock`.stock_id and branch_id = branch.branch_id)) AS unitused,\n";
         $sql .= "sum((SELECT SUM(COALESCE(total,0)) FROM item where stock_id=`itstock`.stock_id and branch_id = branch.branch_id)) AS totalused,\n";
         $sql .= "sum((SELECT SUM(COALESCE(unit,0)) FROM itbroken where stock_id=`itstock`.stock_id)) as unitbroken,\n";
         $sql .= "sum((SELECT SUM(COALESCE(total,0)) FROM itbroken where stock_id=`itstock`.stock_id)) as totalbroken,\n";
         $sql .= "`itstock`.`datepurchased` FROM `itstock`\n";  
         
         $sql .= "LEFT JOIN `itused` ON `itused`.`stock_id` = `itstock`.`stock_id`\n";
         $sql .= "LEFT JOIN `branch` ON `branch`.`branch_id` = `itused`.`branch_id`\n";
         $sql .= "WHERE `itstock`.`ittype_id`=".$cat."\n";
         if($start != '' && $start != null){
            $sql .= "AND DATE(itstock.datepurchased) >='".date("Y-m-d", strtotime($start))."'\n";
         }
         if($end != '' && $end != null){
            $sql .= "AND DATE(itstock.datepurchased) <='".date("Y-m-d", strtotime($end))."'\n";
         }
         if($branch != '' && $branch != null)
         {
            $sql .= "AND itused.branch_id='".db_clean($branch)."'\n";
         }
         $sql .= "GROUP BY `itstock`.`stock_id`\n";
         $sql .= "ORDER BY `itstock`.`item`";  
         return $this->db->query($sql);
     }
     
	 function getStockList($item = '', $datepurchase = ''){
         $sql = "SELECT `itstock`.`stock_id`, `ittype`.`ittype`, `model`.`model` , `itstock`.`item` , itstock.unit, `itstock`.`cpu`,\n";
         $sql .= "(SELECT SUM(COALESCE(unit,0)) FROM itused where stock_id=`itstock`.stock_id) AS unitused,\n";
         $sql .= "(SELECT SUM(COALESCE(unit,0)) FROM itbroken where stock_id=`itstock`.stock_id) as unitbroken,\n";
         $sql .= "`itstock`.`datepurchased`, `itstock`.`createdby` , `itstock`.`remark` FROM `itstock`, `model`, `ittype`\n";
         $sql .= " WHERE `itstock`.`model_id`=`model`.`model_id` AND `itstock`.`ittype_id`=`ittype`.`ittype_id`\n";
         if($item != '' && $item != null)
         {
            $sql .= "AND `itstock`.`item` LIKE '%".$item."%'\n";
         }
         if($datepurchase != '' && $datepurchase != null)
         {
            $datepurchase = date("Y-m-d", strtotime($datepurchase));
            $sql .= "AND DATE(`itstock`.`datepurchased`)='".$datepurchase."'\n";
         }
         $sql .= "GROUP BY `itstock`.`stock_id`\n";
         $sql .= "ORDER BY `itstock`.`item`";
         $query = $this->db->query($sql);
         return $query->result();
	 }
     
      function getItemsBroken($start = '', $end = '', $exp = false){
         if($exp)
            $this->db->select("stock.datepurchased, ittype.ittype as category, model.model, stock.item, broken.label, broken.unit, broken.cpu, broken.total,broken.note As remark");
         else
            $this->db->select("broken.broken_id, stock.datepurchased, ittype.ittype as category, model.model, stock.item, broken.label, broken.unit, broken.cpu, broken.total,broken.note As remark");
	     $this->db->from('itbroken as broken');
         $this->db->join('itstock as stock','broken.stock_id = stock.stock_id');
         $this->db->join('model', 'stock.model_id = model.model_id');
         $this->db->join('ittype','ittype.ittype_id = stock.ittype_id');
         if($start != '' && $start != null){
            $this->db->where('DATE(stock.datepurchased) >=', date("Y-m-d", strtotime($start)));
         }
         if($end != '' && $end != null){
            $this->db->where('DATE(stock.datepurchased) <=', date("Y-m-d", strtotime($end)));
         }
         $this->db->order_by("stock.item");
         $query = $this->db->get();
         //return $query->result();
         return $query;
	 }
     
     function getItemsUsed($start = '', $end = '', $branch = '', $exp = false, $ittype='', $model='', $location=''){
         if($exp)
            $this->db->select("item.dateused, ittype.ittype as category, model.model, stock.item, item.label, item.unit, item.cpu, item.total, status.status,  stock.createdby, branch.branch, location.location, item.staff,item.note As remark");
         else
            $this->db->select("item.stock_id, stock.createdby, item.itused_id, ittype.ittype as category, model.model, item.label, item.dateused, ittype.ittype, stock.item, item.unit, item.cpu, item.total, status.status, branch.branch, location.location, item.staff,item.note As remark");
               
	     $this->db->from('itused as item');
         $this->db->join('itstock as stock','item.stock_id = stock.stock_id');
         $this->db->join('model', 'stock.model_id = model.model_id');
         $this->db->join('ittype','ittype.ittype_id = stock.ittype_id');
         $this->db->join('status','item.status_id = status.status_id');
         $this->db->join('branch','item.branch_id = branch.branch_id', 'LEFT');
         $this->db->join('location','item.location_id = location.location_id', 'LEFT');
         if($start != '' && $start != null){
            $this->db->where('DATE(item.dateused) >=', date("Y-m-d", strtotime($start)));
         }
         if($end != '' && $end != null){
            $this->db->where('DATE(item.dateused) <=', date("Y-m-d", strtotime($end)));
         }
         if($branch != '' && $branch != null)
         {
            $this->db->where('item.branch_id', db_clean($branch));
         }
         if($location != '' && $location != null)
         {
            $this->db->where('item.location_id', id_clean($location));
         }
         if($ittype != '' && $ittype != null)
         {
            $this->db->where('stock.ittype_id', id_clean($ittype));
         }
         if($model != '' && $model != null)
         {
            $this->db->where('stock.model_id', id_clean($model));
         }
         $this->db->order_by("branch.branch");
         $query = $this->db->get();
         //return $query->result();
         return $query;
	 }
     
     function getReportPrinting($cat = '', $model = '', $branch = '', $dept = '')
     {
         $sql = "SELECT `itstock`.`stock_id`,`ittype`.`ittype`,`model`.`model`,`itstock`.`item`,`itstock`.`unit`,`itstock`.`total`,\n";
         $sql .= "sum((SELECT SUM(COALESCE(unit,0)) FROM itused where stock_id=`itstock`.stock_id and branch_id = branch.branch_id)) AS unitused,\n";
         $sql .= "sum((SELECT SUM(COALESCE(total,0)) FROM itused where stock_id=`itstock`.stock_id and branch_id = branch.branch_id)) AS totalused,\n";
         $sql .= "sum((SELECT SUM(COALESCE(unit,0)) FROM itbroken where stock_id=`itstock`.stock_id)) as unitbroken,\n";
         $sql .= "sum((SELECT SUM(COALESCE(total,0)) FROM itbroken where stock_id=`itstock`.stock_id)) as totalbroken,\n";
         $sql .= "`itstock`.`datepurchased` FROM `itstock`\n";  
         
         $sql .= "LEFT JOIN `ittype` ON `ittype`.`ittype_id` = `itstock`.`ittype_id`\n";
         $sql .= "LEFT JOIN `model` ON `model`.`model_id` = `itstock`.`model_id`\n";
         $sql .= "LEFT JOIN `itused` ON `itused`.`stock_id` = `itstock`.`stock_id`\n";
         $sql .= "LEFT JOIN `branch` ON `branch`.`branch_id` = `itused`.`branch_id`\n";
         
         if($cat != '' && $cat != null){
            $sql .= "WHERE `itstock`.`ittype_id`=".$cat."\n";
         }
         if($model != '' && $model != null){
            if (strpos($sql,'WHERE') !== false)
                $sql .= "AND ";
            else
                $sql .= " WHERE ";
            
            $sql .= "`itstock`.`model_id`=".$model."\n";
         }
         
         if($branch != '' && $branch != null)
         {
            if (strpos($sql,'WHERE') !== false)
                $sql .= "AND ";
            else
                $sql .= " WHERE ";

            $sql .= "itused.branch_id='".db_clean($branch)."'\n";
         }
         
         if($dept != '' && $dept != null)
         {
            if (strpos($sql,'WHERE') !== false)
                $sql .= "AND ";
            else
                $sql .= " WHERE ";

            $sql .= "itused.location_id=".db_clean($dept)."\n";
         }
         $sql .= "GROUP BY `itstock`.`stock_id`\n";
         $sql .= "ORDER BY `itstock`.`item`";  
         return $this->db->query($sql);
     }
     
     function getStockById($id){
         $data = array();
         $sql = "SELECT `itstock`.*, `itused`.`itused_id`, \n";
         $sql .= "(SELECT SUM(COALESCE(unit,0)) FROM itused where stock_id=`itstock`.stock_id) AS unitused,\n";
         $sql .= "(SELECT SUM(COALESCE(unit,0)) FROM itbroken where stock_id=`itstock`.stock_id) as unitbroken\n";
         $sql .= "FROM `itstock`\n";
         $sql .= "LEFT JOIN `itused` ON `itused`.`stock_id` = `itstock`.`stock_id`\n";
         $sql .= "WHERE `itstock`.`stock_id`=".id_clean($id)." LIMIT 0,1";
         $query = $this->db->query($sql);
         if ($query->num_rows() > 0){
            $data = $query->row_array();
         }
	     $query->free_result();    
	     return $data;    
	 }
     
     function addEditStock($id=0){
		$data = array( 
			'item' => db_clean($_POST['item']),
            'unit' => id_clean($_POST['unit']),
            'cpu' => id_clean($_POST['cpu']),
            'total'=>id_clean($_POST['unit'] * $_POST['cpu']), 
            'ittype_id' => id_clean($_POST['ittype_id']),
            'model_id' => id_clean($_POST['model_id']),
            'datepurchased' => db_clean($_POST['datepurchased']),
            'createdby' => db_clean($_SESSION['username']),
            'remark' => $_POST['remark'],
			'activate' => db_clean(isset($_POST['activate']) ? 1 : 0)
		);
        if($id > 0)
        {
            $this->db->set('modified', 'NOW()', FALSE);
            $this->db->set('status_id', id_clean($_POST['status_id']));
            $this->db->where('stock_id', $id);
            $this->db->update('itstock', $data);
            
            if(id_clean($_POST['status_id']) == 2 || id_clean($_POST['status_id']) == 4)
            {
                $this->addEditItem($id);
            }
            elseif(id_clean($_POST['status_id']) == 3)
            {
                $this->addEditBroken($id);
            }
            else
            {
                //$this->deleteStockFromItem($id);
                //$this->deleteStockFromBroken($id);
            }
                
        }else{
            $this->db->set('created', 'NOW()', FALSE);
            $this->db->insert('itstock', $data); 
            $id = $this->db->insert_id();
              
            if(id_clean($_POST['status_id']) == 2 || id_clean($_POST['status_id']) == 4)
            {
                $this->addEditItem($id);
            }
            elseif(id_clean($_POST['status_id']) == 3)
            {
                $this->addEditBroken($id);
            }
        }	 
            
	 }
     
     function addEditItem($stockId=0, $itemId=0)
     {
        $data = array(
            'stock_id'=>$stockId,
            'dateused'=>db_clean($_POST['dateused']),
            'label'=>db_clean($_POST['label']),
            'unit' => isset($_POST['unitbroken']) ? id_clean($_POST['unitused']) - id_clean($_POST['unitbroken']) : id_clean($_POST['unitused']),
            'cpu' => id_clean($_POST['cpu']),
            'total'=>id_clean($_POST['unitused'] * $_POST['cpu']), 
            'branch_id'=>db_clean($_POST['branch_id']),
            'location_id'=>id_clean($_POST['location_id']),
            'note'=>isset($_POST['note']) ? db_clean($_POST['note']) : null,
            'staff'=>db_clean($_POST['staff'])            
        );  
        if($itemId > 0)
        {
            if(id_clean($_POST['status_id']) == 3)
            {
                $this->addEditBroken($stockId);
                $this->db->set('status_id', id_clean($_POST['status']));
            }
            else{
                $this->db->set('status_id', id_clean($_POST['status_id']));
            }
            $this->db->where('stock_id', $stockId);
            $this->db->where('itused_id', $itemId);
            $this->db->update('itused', $data);
        }
        else{
            $this->db->set('status_id', id_clean($_POST['status_id']));
            $this->db->insert('itused', $data);
        }  
     }
     
     function addEditBroken($stockId=0)
     {
        $data = array(
            'stock_id'=>$stockId,
            'label' => isset($_POST['labelbroken']) ? id_clean($_POST['labelbroken']) : null,
            'unit' => isset($_POST['unitbroken']) ? id_clean($_POST['unitbroken']) : 0,
            'cpu' => id_clean($_POST['cpu']),
            'note'=>isset($_POST['notebroken']) ? db_clean($_POST['notebroken']) : null,
            'total'=>id_clean($_POST['unitbroken'] * $_POST['cpu'])
        );
        
        $this->db->set('datebroken', 'NOW()', FALSE);
        if($stockId > 0)
        {
            $id = $this->getStockIdFromBroken($stockId);
            if($id > 0)
            {
                $this->db->where('stock_id', $stockId);
                $this->db->update('itbroken', $data);   
            }  
            else{$this->db->insert('itbroken', $data);}  
        }else{
            $this->db->insert('itbroken', $data);   
        }
     }
     
     function getStockIdFromItem($id)
     {
        //$this->db->where('stock_id', $id);
        $query = $this->db->query('SELECT stock_id FROM item WHERE stock_id='.$id);
        $row = $query->row_array();
        return (is_array($row) && count($row) ) ? $row['stock_id'] : 0;
     }
	 
     function getStockIdFromBroken($id)
     {
        $query = $this->db->query('SELECT stock_id FROM itbroken WHERE stock_id='.$id);
        $row = $query->row_array();
        return (is_array($row) && count($row) ) ? $row['stock_id'] : 0;
     }
	  
	 function deleteStock($id){
	 	$this->db->where('stock_id', id_clean($id));
		$this->db->delete('itstock');
	 }
     
     function deleteItem($id){
	 	$this->db->where('itused_id', id_clean($id));
		$this->db->delete('itused');	
	 }
     
     function deleteBroken($id)
     {
    	$this->db->where('broken_id', id_clean($id));
		$this->db->delete('itbroken');
     }
     
     function setStatusInStock($id)
     {
        $this->db->set('status_id',1);
        $this->db->where('stock_id', id_clean($id));
        $this->db->update('itstock');
     }
 }